1. Field of the Invention
The present invention relates generally to databases, and more particularly, to the access of data stored in columns of relational database tables.
2. Description of Related Art
Relational databases are a common tool for storing, administrating and querying data in an ordered form. Conventionally, relational databases are based on table structures in which data are organized in columns and rows. One row is typically called a data set or a data record, and the columns typically contain different attributes for the individual data sets.
FIG. 1A illustrates a prior art example of a conventional data table. In the present illustration, data relating to an individual are stored in a data table 110 having rows 112_1 through 112_x, and columns 114_1 through 114_x. Each row in data table 110 corresponds to an individual. Each of the columns corresponds to a different attribute for the individual. For example, column 114_1 contains surname data, column 114_2 contains first name data, column 114_3 contains phone number data, column 114_4 contains fax number data, and so on.
Defining relational database tables typically entails defining database table columns as to the attributes of the data stored in each column, with each column containing data of the same data type, such as integer, boolean, varchar, float, double, and other data types. A relational database table, therefore, typically represents the sum of the definition of its columns.
In FIG. 1A, each of the columns 114_1 through 114_4 contain data stored as a designated data type. For example, data can be stored in columns 114_1 and 114_2 as data type “varchar” whereas data in columns 114_3 and 114_4 can be stored as data type “integer”.
It should be noted that a “data type” is different from a “data format”. The data type defines the manner in which the data is stored, while the data format relates to the manner in which the data is displayed. For example, a date stored as an integer data type can be displayed in the data format of “yy/mm/dd” or “month day year”.
The data that together define the properties of a relational database table are sometimes termed the metadata. The metadata define, and to some extent also limit, the capabilities of the relational database. Typically, data types of the data stored in the columns, once defined, cannot be changed easily and, often, cannot be changed at all.
The metadata are typically stored in a metadata table. The metadata table is often located in a memory structure with the associated database table or database application used in administrating the database table; however, the metadata table can be located in other memory structures as well. In some database applications, the metadata can be viewed and defined by a user using a graphical user interface sometimes called a table design view that includes a definition table.
FIG. 1B illustrates one prior art example of a graphical user interface that includes a definition table 120 having data types for data stored in columns of database table 110 of FIG. 1A. In a definition table, typically each row corresponds to a column in the database table defined by the definition table.
For example, in definition table 120, column 122_1 defines the name of each column in database table 110 and column 122_2 defines the data type of the data stored in each column. Thus, the data type of a particular data entry in a database table, such as in database table 110 of FIG. 1A, is not readily observable to a user and can be displayed in a data format that may not be definitive of the underlying data type in which the data is stored.
The contents of database tables can be queried and accessed in a variety of ways. The so-called structured query language, or SQL, is currently a widely used method for querying databases. Using SQL commands, a user can, for example, look for people whose name starts with a “P” and who have a phone number ending with “1”.
The data stored in the database tables are typically accessed and managed by programs called database drivers. Database drivers provide the basic functions needed to access database table data, such as the reading and the storing of individual data, columns, rows, etc.
To access the data of individual columns, database drivers typically provide methods that are customized to retrieve data stored according to a certain data type. For each data type, the database driver must offer the user a suitable method for accessing the data, e.g., the data type for which the driver is designed must match the data type of the data stored in the column. The access of column data, therefore, is limited in the sense that the data type of the data stored in the column not only defines the method for retrieval of the data but also defines the data format in which the data is output.
If a user requires data stored in a column of a database to be of a certain data type, different from that in which it is stored, typically, the data type in which the data is stored must first be determined in order to access the data. After the data is accessed, it can be converted to a required data type for use by the user. Frequently, this involves the user manually searching different context menu or tool bar options to identify the data type in which the data is stored in the column and then accessing the data. Further, even if the data type is located it may not accurately reflect the data type of the requested data. This searching process can be time consuming and disruptive to a user. Further, if a program application or component of a program application require the data to be of a different data type from the data type in which the data is stored, typically, the data can not be directly output to the application or component.